﻿IF OBJECT_ID('up_KML_AssetDistributionPoleLines') IS not NULL
	DROP PROC up_KML_AssetDistributionPoleLines 

GO 


CREATE PROCEDURE [dbo].[up_KML_AssetDistributionPoleLines]
	@id int
AS

DECLARE @DistributionPolesList nvarchar(max)
SET @DistributionPolesList = ''

SELECT 
	 @DistributionPolesList = @DistributionPolesList + CONVERT(varchar, [Utility_Asset_DistributionId]) + ','
FROM 
	v_tb_Utility_Asset_Distributions
WHERE
	UtilityFK = @id


declare @settingValue varchar(500)
set @settingValue = (select SettingValue from tb_Settings where SettingId = 26)

declare @template varchar(max);
set @template = '
	<iframe 
		src="{url}{page}?ID=$[ID]" 
		style="width: 325px; height: 400px;"
		frameborder="0"  marginwidth="0" marginheight="0">Pole Position</iframe>
		$[description]'
select @template = replace(@template, '{url}', SettingValue)
from tb_Settings where SettingId = 23

declare @xml varchar(max);
select @xml = replace(convert(varchar(max),
(select 

	 'Pole Position Desktop' as 'Document/name'

	--Style for lines between poles
	,(select
		'AssetDistributionLine' as '@id'
		,.75 as 'IconStyle/scale'
		-- brown
		,'5' as 'LineStyle/width'
		for xml path('Style'), type) as 'Document'
	
	-- point: Distribution Pole (Asset) 
	
	,(
		SELECT 
			CONVERT(varchar, Assets.Utility_Asset_DistributionId) as 'name'
			,'#AssetDistributionLine' as 'styleUrl'
			,Convert(varchar, Assets.Longitude, 128) + ',' + Convert(varchar, Assets.Latitude, 128) + ',0 ' +
				CASE WHEN Assets.ParentAssetFk1 IS NOT NULL THEN Convert(varchar, ParentAsset1.Longitude, 128) + ',' + Convert(varchar, ParentAsset1.Latitude, 128) + ',0 ' ELSE '' END + 
				CASE WHEN Assets.ParentAssetFk2 IS NOT NULL THEN Convert(varchar, ParentAsset2.Longitude, 128) + ',' + Convert(varchar, ParentAsset2.Latitude, 128) + ',0 ' ELSE '' END + 
				CASE WHEN Assets.ParentAssetFk3 IS NOT NULL THEN Convert(varchar, ParentAsset3.Longitude, 128) + ',' + Convert(varchar, ParentAsset3.Latitude, 128) + ',0 ' ELSE '' END +
				CASE WHEN Assets.ParentAssetFk4 IS NOT NULL THEN Convert(varchar, ParentAsset4.Longitude, 128) + ',' + Convert(varchar, ParentAsset4.Latitude, 128) + ',0 ' ELSE '' END +
				CASE WHEN Assets.ParentAssetFk5 IS NOT NULL THEN Convert(varchar, ParentAsset5.Longitude, 128) + ',' + Convert(varchar, ParentAsset5.Latitude, 128) + ',0 ' ELSE '' END AS 'LineString/coordinates'
		FROM 
			(select * from dbo.fn_Split(@DistributionPolesList, default)) as List
		JOIN tb_Utility_Asset_Distributions Assets
			ON Assets.Utility_Asset_DistributionId = List.Value
		LEFT JOIN tb_Utility_Asset_Distributions ParentAsset1
			ON Assets.ParentAssetFk1 = ParentAsset1.Utility_Asset_DistributionId
		LEFT JOIN tb_Utility_Asset_Distributions ParentAsset2
			ON Assets.ParentAssetFk2 = ParentAsset2.Utility_Asset_DistributionId
		LEFT JOIN tb_Utility_Asset_Distributions ParentAsset3
			ON Assets.ParentAssetFk3 = ParentAsset3.Utility_Asset_DistributionId
		LEFT JOIN tb_Utility_Asset_Distributions ParentAsset4
			ON Assets.ParentAssetFk4 = ParentAsset4.Utility_Asset_DistributionId
		LEFT JOIN tb_Utility_Asset_Distributions ParentAsset5
			ON Assets.ParentAssetFk5 = ParentAsset5.Utility_Asset_DistributionId
		WHERE
			(Assets.ParentAssetFk1 IS NOT NULL OR Assets.ParentAssetFk2 IS NOT NULL
			 OR Assets.ParentAssetFk3 IS NOT NULL OR Assets.ParentAssetFk4 IS NOT NULL
			 OR Assets.ParentAssetFk5 IS NOT NULL)
		for xml path('Placemark'), TYPE) as 'Document'	

for xml path('kml'))), '<kml>', '<?xml version="1.0" encoding="UTF-8"?><kml xmlns="http://earth.google.com/kml/2.2">')

select @xml;


 